package yc.tool.dbmigrate.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import yc.tool.dbmigrate.common.Constant;
import yc.tool.dbmigrate.conf.SyncPropConfigurer;
import yc.tool.dbmigrate.entity.FieldInfo;
import yc.tool.dbmigrate.entity.TableInfo;
import yc.tool.dbmigrate.exception.SyncConfigException;

@Component
public class CommonDao {
//	private final Logger log = Logger.getLogger(this.getClass());
	private final Logger log = LoggerFactory.getLogger(this.getClass());
	
	@Resource(name="jdbcTemplate_sqlserver_source")
    private JdbcTemplate jdbcTemplate_sqlserver_source;
	@Resource(name="jdbcTemplate_sqlserver_tag")
    private JdbcTemplate jdbcTemplate_sqlserver_tag;
	
	@Resource(name="jdbcTemplate_oracle_source")
    private JdbcTemplate jdbcTemplate_oracle_source;
	@Resource(name="jdbcTemplate_oracle_tag")
    private JdbcTemplate jdbcTemplate_oracle_tag;
	
	@Resource(name="jdbcTemplate_mysql_source")
    private JdbcTemplate jdbcTemplate_mysql_source;
	@Resource(name="jdbcTemplate_mysql_tag")
    private JdbcTemplate jdbcTemplate_mysql_tag;
	
	//源数据库连接
	private JdbcTemplate SourceJdbc;
	//目标数据库连接
	private JdbcTemplate TagJdbc;
	
	public CommonDao() {
		switch(SyncPropConfigurer.SourceDbType) {
			case Constant.DBTYPE_SQLSERVER:
				SourceJdbc = jdbcTemplate_sqlserver_source;
				break;
			case Constant.DBTYPE_ORACLE:
				SourceJdbc = jdbcTemplate_oracle_source;
				break;
			case Constant.DBTYPE_MYSQL:
				SourceJdbc = jdbcTemplate_mysql_source;
				break;
			default:
				throw new SyncConfigException("源数据库类型配置有误");
		}
		switch(SyncPropConfigurer.TagDbType) {
			case Constant.DBTYPE_SQLSERVER:
				TagJdbc = jdbcTemplate_sqlserver_tag;
				break;
			case Constant.DBTYPE_ORACLE:
				TagJdbc = jdbcTemplate_oracle_tag;
				break;
			case Constant.DBTYPE_MYSQL:
				TagJdbc = jdbcTemplate_mysql_tag;
				break;
			default:
				throw new SyncConfigException("目标数据库类型配置有误");
		}
	}
	
	
	/**
	 * 查看表数据数量
	 * @param tableInfo
	 * @return
	 */
	public Integer getTotal(TableInfo tableInfo) {
		String countSql = "select count(*) from " + tableInfo.getSourceTableName();
		return SourceJdbc.queryForObject(countSql, Integer.class);
	}
	
	/**
	 * 查询一页数据
	 * @param tableInfo
	 * @param pageNumber
	 * @return
	 */
	public List<Map<String, Object>> selectPageData(TableInfo tableInfo, Integer pageNumber){
		StringBuffer sb = new StringBuffer();
		if (Constant.DBTYPE_SQLSERVER.equals(SyncPropConfigurer.SourceDbType)) {
			
			sb.append("SELECT TOP ");
			sb.append(Constant.PageSize);
			sb.append(" ");
			
			List<FieldInfo> fields = tableInfo.getFieldInfos();
			for (int i = 0; i < fields.size(); i++) {
				sb.append(handleKeyWord_s(fields.get(i).getSourceFieldName()));
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			sb.append(" from ");
			sb.append(tableInfo.getSourceTableName());
			sb.append(" where ");
			sb.append(tableInfo.getSourceTableIdName());
			sb.append(" NOT IN (SELECT TOP ");
			sb.append((pageNumber-1)*Constant.PageSize);
			sb.append(" ");
			sb.append(tableInfo.getSourceTableIdName());
			sb.append(" FROM ");
			sb.append(tableInfo.getSourceTableName());
			sb.append(" ORDER BY ");
			sb.append(tableInfo.getSourceTableIdName());
			sb.append(") ORDER BY ");
			sb.append(tableInfo.getSourceTableIdName());
		} else if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.SourceDbType)) {
//			Select * from 
			sb.append("SELECT ");
			List<FieldInfo> fields = tableInfo.getFieldInfos();
			for (int i = 0; i < fields.size(); i++) {
				sb.append(handleKeyWord_s(fields.get(i).getSourceFieldName()));
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			sb.append(" from ");
			sb.append(tableInfo.getSourceTableName());
			sb.append(" where ");
			sb.append(tableInfo.getSourceTableIdName());
			sb.append(" > ");
			if ("AccountBatConfig".equals(tableInfo.getSourceTableName())) {
				sb.append(2);
			} else {
				sb.append(100002);
			}
			
		}
		
		
		String selectSql = sb.toString();
		log.info("select sql: {}", selectSql);
		
		return SourceJdbc.queryForList(selectSql);
	}
	
	/**
	 * 批量插入数据
	 * @param tableInfo
	 * @param data
	 * @return
	 */
	public int insertBatData(TableInfo tableInfo, List<Map<String, Object>> data) {
		
		BatchPreparedStatementSetter bps = new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Map<String, Object> itemData = data.get(i);
				setParams(ps, itemData, tableInfo);
			}

			@Override
			public int getBatchSize() {
				return data.size();
			}
			
		};
		
		String sql = getInsertSqlTemplate(tableInfo);
		if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.TagDbType)) {
			TagJdbc.batchUpdate(sql, bps);
		}
		
		return 0;
	}
	
	private void setParams(PreparedStatement ps, Map<String, Object> itemData, TableInfo tableInfo) throws NumberFormatException, SQLException {
		List<FieldInfo> fieldInfo = tableInfo.getFieldInfos();
		
		FieldInfo itemFieldInfo = null;
		String itemTagFieldType = null;
		Object itemTagFieldValue = null;
		for (int i = 0; i < fieldInfo.size(); i++) {
			itemFieldInfo = fieldInfo.get(i);
			itemTagFieldValue = itemData.get(itemFieldInfo.getSourceFieldName());
			itemTagFieldType = itemFieldInfo.getTagFieldType().toLowerCase();
			
			if (itemTagFieldType.indexOf("int") != -1) {
				if (null != itemTagFieldValue) {
					ps.setInt(i+1, Integer.parseInt(itemTagFieldValue.toString()));
				} else {
					ps.setString(i+1, null);
				}
			} else if (itemTagFieldType.indexOf("char") != -1 || itemTagFieldType.indexOf("text") != -1 || itemTagFieldType.indexOf("clob") != -1) {
				if (itemFieldInfo.getTagFieldNullable()) {
					ps.setString(i+1,(itemTagFieldValue==null)?null:itemTagFieldValue.toString());
				} else {
					ps.setString(i+1,(null == itemTagFieldValue || "".equals(itemTagFieldValue.toString()))?" ":itemTagFieldValue.toString());
				}
			} else if (itemTagFieldType.indexOf("date") != -1 || itemTagFieldType.indexOf("timestamp") != -1) {//可能出错
				ps.setTimestamp(i+1, (itemTagFieldValue==null)?null:((Timestamp)itemTagFieldValue));
			} else if (itemTagFieldType.indexOf("number") != -1 || itemTagFieldType.indexOf("float") != -1 || itemTagFieldType.indexOf("real") != -1 || itemTagFieldType.indexOf("decimal") != -1) {
				if (null != itemTagFieldValue) {
					ps.setDouble(i+1, (itemTagFieldValue==null)?null:((Double)itemTagFieldValue));//可能出错
				} else {
					ps.setString(i+1, null);
				}
			} else if (itemTagFieldType.indexOf("bit") != -1) {
				if (null != itemTagFieldValue) {
//					ps.setBoolean(i+1, (boolean)itemTagFieldValue);
					ps.setBoolean(i+1, (((BigDecimal)itemTagFieldValue).intValue() == 0)?false:true);
				} else {
					ps.setString(i+1, null);
				}
				
			}
		}
	}
		
	
	
	
	
	/**
	 * 插入数据
	 * @param tableInfo
	 * @param data
	 * @return	插入失败的数量
	 */
	public int insertData(TableInfo tableInfo, List<Map<String, Object>> data) {
		if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.TagDbType)) {
			return insertDate_o(tableInfo, data);
		}
		return 0;
	}
	
	@Transactional("txManager_o")
	private int insertDate_o(TableInfo tableInfo, List<Map<String, Object>> data) {
		int wrong_num = 0;
		String sql = null;
		for (Map<String, Object> map : data) {
			sql = getInsertSql(tableInfo, map);
			try {
				TagJdbc.execute(sql);
			} catch (Exception e) {
				log.info("插入失败,sql" + sql, e);
				wrong_num ++;
			}
			
		}
		return wrong_num;
	}
	
	/**
	 * 拼接insert语句
	 * @param tableInfo
	 * @param data
	 * @return
	 */
	private String getInsertSql(TableInfo tableInfo, Map<String, Object> data) {
		StringBuffer sb = new StringBuffer();
		if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.TagDbType)) {
			sb.append("insert into ");
			sb.append(tableInfo.getTagTableName());
			sb.append("(");
			
			List<FieldInfo> fields = tableInfo.getFieldInfos();
			for (int i = 0; i < fields.size(); i++) {
				sb.append(handleKeyWord_o(fields.get(i).getTagFieldName()));
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			
			sb.append(") values(");
			//拼接数据
			FieldInfo fi = null;
			for (int i = 0; i < fields.size(); i++) {
				fi = fields.get(i);
				sb.append(tagFieldFormat(data.get(fields.get(i).getSourceFieldName()), fi));
				
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			sb.append(")");
			
		}
		
		return sb.toString();
	}
	
	/**
	 * 拼接insert 模板。 insert into a(x,x,x) values(?,?,?)
	 * @param tableInfo
	 * @return
	 */
	private String getInsertSqlTemplate(TableInfo tableInfo) {
		StringBuffer sb = new StringBuffer();
		if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.TagDbType)) {
			sb.append("insert into ");
			sb.append(tableInfo.getTagTableName());
			sb.append("(");
			
			List<FieldInfo> fields = tableInfo.getFieldInfos();
			for (int i = 0; i < fields.size(); i++) {
				sb.append(handleKeyWord_o(fields.get(i).getTagFieldName()));
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			
			sb.append(") values(");
			for (int i = 0; i < fields.size(); i++) {
				sb.append("?");
				if (i < (fields.size() - 1)) {
					sb.append(", ");
				}
			}
			sb.append(")");
		}
		
		return sb.toString();
	}
	
	//--------类型转换-----------
	private String tagFieldFormat(Object obj, FieldInfo fieldInfo) {
		String res = null;
		if (null == obj) {
			if (fieldInfo.getTagFieldNullable()) {
				return null;
			} else {
				return "' '";
			}
		}
		
		String sourceType = fieldInfo.getSourceFieldType().toLowerCase();
		String tagType = fieldInfo.getTagFieldType().toLowerCase();
		
		if (sourceType.indexOf("date") != -1) {//日期类型
			Date date = (Date)obj;
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			if (Constant.DBTYPE_ORACLE.equals(SyncPropConfigurer.TagDbType)) {
				res = "TO_DATE('" + sdf.format(date) + "', 'yyyy-mm-dd hh24:mi:ss')";
			}
		} else if (sourceType.indexOf("varchar") != -1 || sourceType.indexOf("text") != -1) {//字符串类型
			res = "'" + (("".equals(obj.toString()) && !fieldInfo.getTagFieldNullable())?" ":obj.toString()) + "'";
		} else if (sourceType.indexOf("bit") != -1) {
			if ((boolean)obj) {
				res = "1";
			} else {
				res = "0";
			}
		} else {
			res = obj.toString();
		}
		
		
		return res;
	}
	
	//---------关键字处理--------------
	private String handleKeyWord_s(String fieldName) {
		for (String item : Constant.KEYWORD_SQLSERVER) {
			if (item.equals(fieldName.toLowerCase())) {
				return "\""+fieldName.toUpperCase()+"\"";
			}
		}
		return fieldName;
	}
	private String handleKeyWord_o(String fieldName) {
		for (String item : Constant.KEYWORD_ORACLE) {
			if (item.equals(fieldName.toLowerCase())) {
				return "\""+fieldName.toUpperCase()+"\"";
			}
		}
		return fieldName;
	}
	private String handleKeyWord_m(String fieldName) {
		for (String item : Constant.KEYWORD_MYSQL) {
			if (item.equals(fieldName.toLowerCase())) {
				return "`"+fieldName.toUpperCase()+"`";
			}
		}
		return fieldName;
	}
}
